package com.bingo.generate.comm.service;


import com.bingo.generate.comm.entity.ColInfo;
import com.bingo.generate.comm.entity.TableInfo;
import com.bingo.generate.comm.util.JdbcUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;

import java.util.ArrayList;
import java.util.List;

public class GenerateDbService {

    public static GenerateDbService ins=new GenerateDbService();

    public static String dbName;

    private GenerateDbService() {
    }

    public List<String> searchTableName(String dbNameKey, String tableNameKey, String commentKey){
        if(dbNameKey==null){
            dbNameKey="%";
        }
        dbNameKey=dbNameKey.replace("*","%");
        if(tableNameKey==null){
            tableNameKey="%";
        }
        tableNameKey=tableNameKey.replace("*","%");
        if(commentKey==null){
            commentKey="%";
        }
        commentKey=commentKey.replace("*","%");

        StringBuilder strBud=new StringBuilder("select ");
        strBud.append(" distinct TABLE_NAME ");
        //strBud.append("TABLE_SCHEMA db_name,TABLE_NAME,column_name colName ");
        //strBud.append(",DATA_TYPE type,IS_NULLABLE,COLUMN_DEFAULT defaultVal,column_comment comment ");
        strBud.append(" FROM information_schema. COLUMNS where table_schema like '"+dbNameKey+"'");
        strBud.append(" and table_name like '"+tableNameKey+"'");
        strBud.append(" and (column_comment like '"+commentKey+"' or column_name like '"+commentKey+"' ) ");
        String sql=strBud.toString();
        //System.out.println(sql);
        //  将*替换为%
        if(JdbcUtil.ins==null){
            throw new RuntimeException("没有配置数据库");
        }
        List<String> res= JdbcUtil.ins.queryForList(sql,String.class);
        return res;
    }

    public List<TableInfo> searchTable(String dbName){
        String sql = "select table_name tableName,table_comment tableComment from information_schema.tables where table_schema='"+dbName+"';";
        RowMapper rw= BeanPropertyRowMapper.newInstance(TableInfo.class);
        List<TableInfo> res= JdbcUtil.ins.query(sql , rw );
        return res;
    }

    //  根据关键字生成md
    public String createMdByKey(String dbNameKey,String tableNameKey){
        if(dbNameKey==null){
            dbNameKey="%";
        }
        dbNameKey=dbNameKey.replace("*","%");
        if(tableNameKey==null){
            tableNameKey="%";
        }
        tableNameKey=tableNameKey.replace("*","%");
        //  查找是否数量过大
        {
            /*StringBuilder strBud = new StringBuilder("select ");
            strBud.append(" distinct TABLE_NAME ");
            strBud.append(" FROM information_schema. COLUMNS where table_schema like '"+dbNameKey+"'");
            strBud.append(" and table_name like '"+tableNameKey+"'");
            String sql=strBud.toString();
            System.out.println(sql);
            String countSql=String.format("select count(*) from ( %s ) temp_tb",sql);
            Integer count=JdbcUtil.ins.queryForObject(countSql,Integer.class);*/
            List<String> tbs=searchTableName(dbNameKey,tableNameKey,null);
            if(tbs.size()==0){
                return "你输入的关键字不正确";
            }
            if(tbs.size()>1){
                return "你输入的关键字太过模糊，找到多个表:"+tbs.toString();
            }
        }

        StringBuilder strBud=new StringBuilder("select ");
        strBud.append("TABLE_SCHEMA db_name,TABLE_NAME,column_name colName ");
        strBud.append(",DATA_TYPE type,IS_NULLABLE isNullable,COLUMN_DEFAULT defaultVal,column_comment comment ");
        strBud.append(" FROM information_schema. COLUMNS where table_schema like '"+dbNameKey+"' and table_name like '"+tableNameKey+"'");
        String sql=strBud.toString();

        RowMapper rw= BeanPropertyRowMapper.newInstance(ColInfo.class);
        //  将*替换为%
        List<ColInfo> list=JdbcUtil.ins.query(sql,rw);
        StringBuilder mdBud=new StringBuilder();
        mdBud.append("|参数名|类型|说明|\n")
        .append("|:-----  |:-----|-----|\n");
        for (ColInfo col : list) {
            mdBud.append(String.format("|%s |%s   |%s  |\n",col.getColName(),col.getType(),col.getComment()));
        }

        return mdBud.toString();
    }



    public List<ColInfo> searchCols(String key){
        if(key.indexOf(".")==-1){
            if(dbName!=null){
                key=dbName+"."+key;
            }else{
                key="*."+key;
            }
        }
        String[] keys= key.split("\\.");
        return searchCols(keys[0],keys[1]);
    }

    //  根据关键字生成md
    public List<ColInfo> searchCols(String dbNameKey, String tableNameKey){
        if(dbNameKey==null){
            if(dbName==null){
                dbNameKey="%";
            }else {
                dbNameKey=dbName;
            }
        }
        dbNameKey=dbNameKey.replace("*","%");
        if(tableNameKey==null){
            tableNameKey="%";
        }
        tableNameKey=tableNameKey.replace("*","%");
        //  查找是否数量过大
        {
            List<String> tbs=searchTableName(dbNameKey,tableNameKey,null);
            if(tbs.size()==0){
                throw new RuntimeException("你输入的关键字不正确");
            }
            if(tbs.size()>1){
                throw new RuntimeException("你输入的关键字太过模糊，找到多个表:"+tbs.toString());
            }
        }

        StringBuilder strBud=new StringBuilder("select ");
        strBud.append("TABLE_SCHEMA db_name,TABLE_NAME,column_name colName ");
        strBud.append(",DATA_TYPE type,IS_NULLABLE isNullable,COLUMN_DEFAULT defaultVal,column_comment comment ");
        strBud.append(",COLUMN_TYPE colType  ");
        strBud.append(" FROM information_schema. COLUMNS where table_schema like '"+dbNameKey+"' and table_name like '"+tableNameKey+"'");
        String sql=strBud.toString();

        RowMapper rw= BeanPropertyRowMapper.newInstance(ColInfo.class);
        //  将*替换为%
        List<ColInfo> list=JdbcUtil.ins.query(sql,rw);
        return list;
    }

    public String tipsAdd(String dbNameKey,String tableNameKey,boolean isName){
        List<ColInfo> list=searchCols(dbNameKey,tableNameKey);
        //  id相关列  非空列   状态字段列  时间列	其他列
        List<ColInfo> allList=new ArrayList<>();
        List<ColInfo> idList=new ArrayList<>();
        List<ColInfo> statusList=new ArrayList<>();
        List<ColInfo> noNullList=new ArrayList<>();
        List<ColInfo> timeList=new ArrayList<>();
        List<ColInfo> otherList=new ArrayList<>();
        for (ColInfo colInfo : list) {
            allList.add(colInfo);
            if(colInfo.getColName().indexOf("id")!=-1||colInfo.getColName().indexOf("Id")!=-1||colInfo.getColName().indexOf("ID")!=-1){
                idList.add(colInfo);
                continue;
            }
            if(colInfo.getColName().indexOf("status")!=-1||colInfo.getColName().indexOf("type")!=-1){
                statusList.add(colInfo);
                continue;
            }
            if(colInfo.getComment()!=null&&colInfo.getComment().indexOf("是否")!=-1){
                statusList.add(colInfo);
                continue;
            }
            if(colInfo.getColName().indexOf("date")!=-1||colInfo.getColName().indexOf("at")!=-1||colInfo.getColName().indexOf("time")!=-1){
                timeList.add(colInfo);
                continue;
            }
            if(colInfo.getIsNullable().equals("NO")){
                noNullList.add(colInfo);
                continue;
            }
            otherList.add(colInfo);
        }

        StringBuilder strBud=new StringBuilder();
        strBud.append("系统自动整理插入提示字段:\n");
        strBud.append("\nid相关字段:"+toStr(idList,isName));
        strBud.append("\n状态字段列:"+toStr(statusList,isName));
        strBud.append("\n时间列:"+toStr(timeList,isName));
        strBud.append("\n非空列:"+toStr(noNullList,isName));
        strBud.append("\n其他列:"+toStr(otherList,isName));
        strBud.append("\n所有列:\n"+toStr(allList,isName));
        return strBud.toString();

    }

    public String toStr(List<ColInfo> colList,boolean isName){
        List<String> strList=new ArrayList();
        if(isName){
            for (ColInfo colInfo : colList) {
                String comment=colInfo.getComment();
                if(comment.length()>7){
                    comment=comment.substring(0,7);
                }
                strList.add(String.format("%s[%s]",colInfo.getColName(),comment));
            }
        }else {
            for (ColInfo colInfo : colList) {
                strList.add(colInfo.getColName());
            }
        }
        StringBuilder strBud=new StringBuilder();
        strBud.append("{\t");
        for (int i = 0; i < strList.size(); i++) {
            /*String table=strList.get(i);
            strBud.append(table).append("\t");
            if(i%7==0){
                strBud.append("\n");
            }*/
            String table=strList.get(i);
            strBud.append(table).append(" , ");
        }
        strBud.append("\t}");
        return strBud.toString();
    }

}
